﻿//using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Shared.Method;
using Shared.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace SharedClientDll
{
    /// <summary>
    /// 专用的NOPI EXCEL操作类
    /// </summary>
    public class YcdExcelBaseClass<T> where T : TableBase, new()
    {

        public int ColumnLength { get; private set; }//列数
        public int RowLength { get; private set; }//行数
        public int MyRowNum { get; private set; }//当前行
        public List<string> FirstRow { get; } = new List<string>();

        //行集合
        private System.Collections.IEnumerator DataRows;

        //有顺序的类属性集合
        private List<PropertyInfo> _listTableItems = new List<PropertyInfo>();

        //初始化EXCEL表
        public bool InitExcelClass(string excelPath, out string msg, int sheetNum = 0)
        {
            try
            {
                msg = "";

                XSSFWorkbook book = null;

                using (FileStream file = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
                {
                    book = new XSSFWorkbook(file);
                }

                //默认获取第一个工作表
                var sheet = book.GetSheetAt(sheetNum);

                //判断行数目，必须大于2行，行号从0开始与表格中不同
                RowLength = sheet.LastRowNum - sheet.FirstRowNum + 1;

                if (RowLength < 2)
                {
                    msg = "数据表行数小于2行";
                    return false;
                }

                //获取行
                DataRows = sheet.GetRowEnumerator();

                //第一行判断
                DataRows.MoveNext();

                //修改行号码
                MyRowNum = sheet.FirstRowNum + 1;

                var firstRow = (XSSFRow)DataRows.Current;

                //获取列的数目
                ColumnLength = firstRow.Cells.Count;

                //默认第一个不为空的行是列名称行

                //获取T对象的所有的公开属性
                var listProperty = typeof(T).GetProperties();


                //判断列名称是否在T中
                for (int i = 0; i < ColumnLength; i++)
                {
                    PropertyInfo info = (from n in listProperty
                                         where n.Name == firstRow.GetCell(i).ToString()
                                         select n).FirstOrDefault();

                    if (info == null)
                    {
                        msg += string.Format("未能在数据表中找到相应的列，Excel列名：{0}", firstRow.GetCell(i).ToString());
                        return false;
                    }

                    _listTableItems.Add(info);
                    FirstRow.Add(info.Name);
                }

                return true;
            }
            catch (Exception ex)
            {
                msg = ex.ToString();
                return false;
            }
        }

        //返回行数据
        public T GetRow(out string msg)
        {
            T tableClass = null;
            msg = "";
            try
            {
                if (DataRows.MoveNext())
                {
                    MyRowNum++;

                    var row = (XSSFRow)DataRows.Current;

                    tableClass = new T();

                    tableClass.SetUpdate();//更新选项

                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        var cell = row.GetCell(i);

                        var property = _listTableItems[i];

                        if (property.PropertyType == typeof(string))
                        {
                            property.SetValue(tableClass, cell.ToString().Trim(), null);
                        }
                        else if (property.PropertyType == typeof(long))
                        {
                            property.SetValue(tableClass, cell.NumericCellValue, null);
                        }
                        else if (property.PropertyType == typeof(int))
                        {
                            property.SetValue(tableClass, cell.NumericCellValue, null);
                        }
                        else if (property.PropertyType == typeof(double))
                        {
                            property.SetValue(tableClass, cell.NumericCellValue, null);
                        }
                        else if (property.PropertyType == typeof(DateTime))
                        {
                            property.SetValue(tableClass, cell.DateCellValue, null);
                        }
                        else if (property.PropertyType == typeof(bool))
                        {
                            property.SetValue(tableClass, cell.BooleanCellValue, null);
                        }
                    }
                }
                else
                {
                    msg = "表内数据已经读取完毕";
                }

            }
            catch (Exception ex)
            {
                msg = ex.Message;

            }

            return tableClass;
        }


    }

    public class YcdExcelStatic
    {

        #region 导出到excel

        /// <summary>
        /// Datable导出成Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file">导出路径(包括文件名与扩展名)</param>
        public static bool TableToExcel(DataTable dt, string file)
        {
            try
            {
                IWorkbook workbook;

                string directoryPath = Path.GetDirectoryName(file);
                if (Directory.Exists(directoryPath) == false)
                {
                    Directory.CreateDirectory(directoryPath);
                }

                string fileExt = Path.GetExtension(file).ToLower();

                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); }
                else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); }
                else { workbook = null; }

                if (workbook == null) { return false; }

                ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);

                //表头  
                IRow row = sheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(dt.Columns[i].ColumnName);
                }

                //数据  
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row1 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell cell = row1.CreateCell(j);
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                    }
                }

                //转为字节数组  
                MemoryStream stream = new MemoryStream();
                workbook.Write(stream);
                var buf = stream.ToArray();

                //保存为Excel文件  
                using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                }

                return true;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("TableToExcel", "导入错误", ex.ToString());
                return false;
            }
        }

        #endregion

        #region EXCEL INSERT

        private static Queue<string> s_excelList = new Queue<string>();
        //private static bool s_finish = false;

        public static DataTable ExcelToTable(string excelPath, out string msg, int sheetNum = 0)
        {
            msg = null;

            try
            {
                //s_finish = false;

                if (File.Exists(excelPath) == false) { throw new Exception("文件不存在"); }

                IWorkbook workbook = null;

                string fileExt = Path.GetExtension(excelPath).ToLower();
                using (FileStream file = File.OpenRead(excelPath))
                {
                    if (fileExt == ".xlsx") workbook = new XSSFWorkbook(file);
                    else if (fileExt == ".xls") workbook = new HSSFWorkbook(file);
                    else throw new Exception("所选文件并非EXCEL文件");
               
                    var sheet = workbook.GetSheetAt(sheetNum);

                    //判断行数目，必须大于2行，行号从0开始与表格中不同
                    int rowLength = sheet.LastRowNum - sheet.FirstRowNum + 1;

                    if (rowLength < 2)
                    {
                        throw new Exception("表行数小于2行");
                    }

                    //获取行
                    var dataRows = sheet.GetRowEnumerator();

                    //第一行判断
                    dataRows.MoveNext();

                    //修改行号码
                    var myRowNum = sheet.FirstRowNum + 1;

                    //第一行数据
                    var excelRowData = (IRow)dataRows.Current;

                    //获取列的数目
                    var columnLength = excelRowData.Cells.Count;

                    if (columnLength <= 1) throw new Exception("列数小于1");

                    //创建一个DataTable
                    DataTable table = new DataTable();
                    
                    for (int i = 0; i < columnLength; i++)
                    {
                        table.Columns.Add(new DataColumn(excelRowData.GetCell(i).ToString()));
                    }

                    //将ROW转换成JSON，存入s_excelList


                    while (dataRows.MoveNext())
                    {
                        DataRow row = table.NewRow();
                        //数据表移动到下一行
                        excelRowData = (IRow)dataRows.Current;

                        for(int j=0;j< columnLength; j++)
                        {
                            if(excelRowData.GetCell(j).CellType== CellType.String)
                            {
                                row[j] = excelRowData.GetCell(j).StringCellValue.Replace(" ", "").Replace(":", "");
                            }
                            else
                            {
                                row[j] = excelRowData.GetCell(j);
                            }
                        }

                        //lock (s_excelList) s_excelList.Enqueue(JsonConvert.SerializeObject(row));

                        table.Rows.Add(row);
                    }

                //s_finish = true;

                //return JsonConvert.SerializeObject(table);
                return table;

                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("YcdExcelStatic.InitExcel", "导入数据错误", ex.ToString());
                msg = ex.ToString();
                //s_finish = true;
                return null;
            }
        }
        #endregion
    }


}
